package database

import (
	"context"
	"database/sql"
	"fmt"
	"strconv"
	"strings"
	"time"

	"github.com/pkg/errors"
	"gorm.io/driver/sqlserver"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"

	"changeme/internal/models"
)

// SQLServerDriverImpl SQL Server数据库驱动实现
type SQLServerDriverImpl struct {
	BaseDriver
	gormDB *gorm.DB
}

// NewSQLServerDriverImpl 创建SQL Server驱动实例
func NewSQLServerDriverImpl() *SQLServerDriverImpl {
	return &SQLServerDriverImpl{
		BaseDriver: BaseDriver{
			Dialect: &SQLServerDialect{},
		},
	}
}

// Connect 连接到SQL Server数据库
func (s *SQLServerDriverImpl) Connect(config *models.DatabaseConfig) error {
	if s.Connected {
		return ErrConnectionExists
	}

	// 构建连接字符串
	dsn := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d",
		config.Host, config.Username, config.Password, config.Port)

	// 添加数据库名称（如果提供）
	if config.Database != "" {
		dsn += fmt.Sprintf(";database=%s", config.Database)
	}

	// 添加其他选项
	if config.Options != "" {
		dsn += ";" + config.Options
	}

	// 配置GORM
	gormConfig := &gorm.Config{
		Logger: logger.Default.LogMode(logger.Silent),
	}

	// 使用GORM连接
	gormDB, err := gorm.Open(sqlserver.Open(dsn), gormConfig)
	if err != nil {
		return errors.Wrap(err, "failed to open SQL Server connection with GORM")
	}

	// 获取底层的sql.DB对象
	sqlDB, err := gormDB.DB()
	if err != nil {
		return errors.Wrap(err, "failed to get underlying sql.DB from GORM")
	}

	// 设置连接池参数
	sqlDB.SetMaxOpenConns(25)
	sqlDB.SetMaxIdleConns(5)
	sqlDB.SetConnMaxLifetime(5 * time.Minute)

	// 测试连接
	if err := sqlDB.Ping(); err != nil {
		sqlDB.Close()
		return errors.Wrap(err, "failed to ping SQL Server database")
	}

	s.DB = sqlDB
	s.gormDB = gormDB
	s.Config = config
	s.Connected = true

	return nil
}

// GetVersion 获取SQL Server版本
func (s *SQLServerDriverImpl) GetVersion() (string, error) {
	if !s.IsConnected() {
		return "", ErrNotConnected
	}

	var version string
	err := s.DB.QueryRow("SELECT @@VERSION").Scan(&version)
	if err != nil {
		return "", errors.Wrap(err, "failed to get SQL Server version")
	}

	return version, nil
}

// ListDatabases 列出所有数据库
func (s *SQLServerDriverImpl) ListDatabases(ctx context.Context) ([]models.DatabaseInfo, error) {
	if !s.IsConnected() {
		return nil, ErrNotConnected
	}

	query := `
		SELECT 
			name,
			CONVERT(VARCHAR(100), DATABASEPROPERTYEX(name, 'Collation')) as collation,
			CONVERT(VARCHAR(100), DATABASEPROPERTYEX(name, 'DefaultLanguage')) as default_language,
			create_date,
			(SELECT SUM(size * 8 * 1024) FROM sys.master_files WHERE database_id = DB_ID(d.name)) as size_bytes
		FROM sys.databases d
		WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
		ORDER BY name
	`

	rows, err := s.DB.QueryContext(ctx, query)
	if err != nil {
		return nil, errors.Wrap(err, "failed to list databases")
	}
	defer rows.Close()

	var databases []models.DatabaseInfo
	for rows.Next() {
		var db models.DatabaseInfo
		var createDate, defaultLanguage sql.NullString
		var sizeBytes sql.NullInt64
		if err := rows.Scan(&db.Name, &db.Collation, &defaultLanguage, &createDate, &sizeBytes); err != nil {
			return nil, errors.Wrap(err, "failed to scan database info")
		}

		if createDate.Valid {
			if db.Comment != "" {
				db.Comment += ", Created: " + createDate.String
			} else {
				db.Comment = "Created: " + createDate.String
			}
		}

		if defaultLanguage.Valid {
			if db.Comment != "" {
				db.Comment += ", Default language: " + defaultLanguage.String
			} else {
				db.Comment = "Default language: " + defaultLanguage.String
			}
		}

		if sizeBytes.Valid {
			db.Size = sizeBytes.Int64
		}

		databases = append(databases, db)
	}

	return databases, nil
}

// ListTables 列出指定数据库的所有表
func (s *SQLServerDriverImpl) ListTables(ctx context.Context, database string) ([]models.TableInfo, error) {
	if !s.IsConnected() {
		return nil, ErrNotConnected
	}

	// 切换到指定数据库
	if s.Config.Database != database {
		// 在当前连接中切换数据库
		if _, err := s.DB.ExecContext(ctx, fmt.Sprintf("USE [%s]", database)); err != nil {
			return nil, errors.Wrapf(err, "failed to switch to database %s", database)
		}
		s.Config.Database = database
	}

	query := `
		SELECT 
			t.name AS table_name,
			s.name AS schema_name,
			CASE 
				WHEN t.type = 'U' THEN 'TABLE'
				WHEN t.type = 'V' THEN 'VIEW'
				ELSE t.type
			END as table_type,
			p.rows AS row_count,
			SUM(a.total_pages) * 8 * 1024 AS data_length,
			SUM(a.used_pages) * 8 * 1024 AS index_length,
			CONVERT(VARCHAR(100), t.create_date, 120) AS created_at,
			ISNULL(ep.value, '') AS comment
		FROM sys.tables t
		JOIN sys.schemas s ON t.schema_id = s.schema_id
		LEFT JOIN sys.indexes i ON t.object_id = i.object_id
		LEFT JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
		LEFT JOIN sys.allocation_units a ON p.partition_id = a.container_id
		LEFT JOIN sys.extended_properties ep ON t.object_id = ep.major_id 
			AND ep.minor_id = 0 
			AND ep.name = 'MS_Description'
		GROUP BY t.name, s.name, t.type, p.rows, t.create_date, ep.value
		ORDER BY table_name
	`

	rows, err := s.DB.QueryContext(ctx, query)
	if err != nil {
		return nil, errors.Wrap(err, "failed to list tables")
	}
	defer rows.Close()

	var tables []models.TableInfo
	for rows.Next() {
		var table models.TableInfo
		var comment sql.NullString
		if err := rows.Scan(
			&table.Name, &table.Schema, &table.Type,
			&table.RowCount, &table.DataLength, &table.IndexLength,
			&table.CreatedAt, &comment,
		); err != nil {
			return nil, errors.Wrap(err, "failed to scan table info")
		}

		table.Engine = "SQL Server"

		if comment.Valid {
			table.Comment = comment.String
		}

		tables = append(tables, table)
	}

	return tables, nil
}

// GetTableColumns 获取表的列信息
func (s *SQLServerDriverImpl) GetTableColumns(ctx context.Context, database, table string) ([]models.ColumnInfo, error) {
	if !s.IsConnected() {
		return nil, ErrNotConnected
	}

	// 切换到指定数据库
	if s.Config.Database != database {
		// 在当前连接中切换数据库
		if _, err := s.DB.ExecContext(ctx, fmt.Sprintf("USE [%s]", database)); err != nil {
			return nil, errors.Wrapf(err, "failed to switch to database %s", database)
		}
		s.Config.Database = database
	}

	// 解析schema和表名
	schema := "dbo"
	tableName := table
	if strings.Contains(table, ".") {
		parts := strings.Split(table, ".")
		schema = parts[0]
		tableName = parts[1]
	}

	query := `
		SELECT 
			c.name AS column_name,
			t.name AS data_type,
			c.max_length,
			c.precision,
			c.scale,
			CASE WHEN c.is_nullable = 1 THEN 'YES' ELSE 'NO' END AS is_nullable,
			CASE WHEN ic.column_id IS NOT NULL THEN 'PRI' ELSE '' END AS column_key,
			c.column_id AS ordinal_position,
			ISNULL(dc.definition, '') AS column_default,
			CASE WHEN c.is_identity = 1 THEN 'auto_increment' ELSE '' END AS extra,
			ISNULL(ep.value, '') AS column_comment,
			t.name + 
			CASE 
				WHEN t.name IN ('char', 'varchar', 'nchar', 'nvarchar') THEN '(' + 
					CASE WHEN c.max_length = -1 THEN 'MAX' 
					WHEN t.name IN ('nchar', 'nvarchar') THEN CAST(c.max_length/2 AS VARCHAR)
					ELSE CAST(c.max_length AS VARCHAR) END + ')'
				WHEN t.name IN ('decimal', 'numeric') THEN '(' + CAST(c.precision AS VARCHAR) + ',' + CAST(c.scale AS VARCHAR) + ')'
				ELSE ''
			END AS full_type
		FROM sys.columns c
		JOIN sys.types t ON c.user_type_id = t.user_type_id
		JOIN sys.tables tb ON c.object_id = tb.object_id
		JOIN sys.schemas s ON tb.schema_id = s.schema_id
		LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
		LEFT JOIN sys.indexes i ON tb.object_id = i.object_id AND i.is_primary_key = 1
		LEFT JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id AND c.column_id = ic.column_id
		LEFT JOIN sys.extended_properties ep ON c.object_id = ep.major_id AND c.column_id = ep.minor_id AND ep.name = 'MS_Description'
		WHERE tb.name = @p1 AND s.name = @p2
		ORDER BY c.column_id
	`

	rows, err := s.DB.QueryContext(ctx, query, tableName, schema)
	if err != nil {
		return nil, errors.Wrap(err, "failed to get table columns")
	}
	defer rows.Close()

	var columns []models.ColumnInfo
	for rows.Next() {
		var column models.ColumnInfo
		var columnName, dataType, isNullable, columnKey, columnDefault, extra, comment, fullType sql.NullString
		var maxLength, precision, scale, position sql.NullInt64

		if err := rows.Scan(
			&columnName, &dataType, &maxLength, &precision, &scale,
			&isNullable, &columnKey, &position, &columnDefault, &extra,
			&comment, &fullType,
		); err != nil {
			return nil, errors.Wrap(err, "failed to scan column info")
		}

		// 设置基本信息
		column.Name = columnName.String
		column.Type = strings.ToLower(dataType.String)
		column.Position = int(position.Int64)
		column.FullType = fullType.String

		// 设置长度、精度和小数位
		if maxLength.Valid {
			if dataType.String == "nchar" || dataType.String == "nvarchar" {
				column.Length = int(maxLength.Int64) / 2 // Unicode字符占用2字节
			} else {
				column.Length = int(maxLength.Int64)
			}
		}
		if precision.Valid {
			column.Precision = int(precision.Int64)
		}
		if scale.Valid {
			column.Scale = int(scale.Int64)
		}

		// 设置NULL约束
		column.IsNullable = isNullable.String == "YES"
		column.NotNull = !column.IsNullable

		// 设置主键
		column.IsPrimaryKey = columnKey.String == "PRI"
		column.PrimaryKey = column.IsPrimaryKey

		// 设置自增
		column.IsAutoIncrement = strings.Contains(extra.String, "auto_increment")
		column.AutoIncrement = column.IsAutoIncrement

		// 设置默认值
		if columnDefault.Valid {
			// 清理默认值中的括号和额外字符
			defValue := columnDefault.String
			defValue = strings.TrimPrefix(defValue, "(")
			defValue = strings.TrimSuffix(defValue, ")")
			defValue = strings.Trim(defValue, "'")
			column.DefaultValue = defValue
		}

		// 设置注释
		if comment.Valid {
			column.Comment = comment.String
		}

		// 其他元数据
		column.Extra = extra.String

		column.RawInfo = map[string]interface{}{
			"columnName":    columnName.String,
			"dataType":      dataType.String,
			"fullType":      fullType.String,
			"isNullable":    isNullable.String,
			"columnKey":     columnKey.String,
			"columnDefault": columnDefault.String,
		}

		columns = append(columns, column)
	}

	// 使用格式化工具处理列信息
	return FormatColumns(columns, models.SQLServer), nil
}

// GetTableIndexes 获取表的索引信息
func (s *SQLServerDriverImpl) GetTableIndexes(ctx context.Context, database, table string) ([]models.IndexInfo, error) {
	if !s.IsConnected() {
		return nil, ErrNotConnected
	}

	// 切换到指定数据库
	if s.Config.Database != database {
		// 在当前连接中切换数据库
		if _, err := s.DB.ExecContext(ctx, fmt.Sprintf("USE [%s]", database)); err != nil {
			return nil, errors.Wrapf(err, "failed to switch to database %s", database)
		}
		s.Config.Database = database
	}

	// 解析schema和表名
	schema := "dbo"
	tableName := table
	if strings.Contains(table, ".") {
		parts := strings.Split(table, ".")
		schema = parts[0]
		tableName = parts[1]
	}

	query := `
		SELECT 
			i.name AS index_name,
			i.type_desc AS index_type,
			i.is_unique,
			i.is_primary_key,
			STRING_AGG(c.name, ',') WITHIN GROUP (ORDER BY ic.key_ordinal) AS column_names,
			ISNULL(ep.value, '') AS comment
		FROM sys.indexes i
		JOIN sys.tables t ON i.object_id = t.object_id
		JOIN sys.schemas s ON t.schema_id = s.schema_id
		JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
		JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
		LEFT JOIN sys.extended_properties ep ON i.object_id = ep.major_id AND i.index_id = ep.minor_id AND ep.name = 'MS_Description'
		WHERE t.name = @p1 AND s.name = @p2
		GROUP BY i.name, i.type_desc, i.is_unique, i.is_primary_key, ep.value
		ORDER BY i.name
	`

	rows, err := s.DB.QueryContext(ctx, query, tableName, schema)
	if err != nil {
		return nil, errors.Wrap(err, "failed to get table indexes")
	}
	defer rows.Close()

	var indexes []models.IndexInfo
	for rows.Next() {
		var index models.IndexInfo
		var name, indexType, columnNames, comment sql.NullString
		var isUnique, isPrimary sql.NullBool

		if err := rows.Scan(
			&name, &indexType, &isUnique, &isPrimary, &columnNames, &comment,
		); err != nil {
			return nil, errors.Wrap(err, "failed to scan index info")
		}

		// 设置基本信息
		index.Name = name.String
		index.Type = indexType.String

		// 设置是否唯一和主键
		if isUnique.Valid {
			index.IsUnique = isUnique.Bool
		}
		if isPrimary.Valid {
			index.IsPrimary = isPrimary.Bool
		}

		// 设置列
		if columnNames.Valid {
			index.Columns = strings.Split(columnNames.String, ",")
		}

		// 设置注释
		if comment.Valid {
			index.Comment = comment.String
		}

		indexes = append(indexes, index)
	}

	return indexes, nil
}

// ExecuteQuery 执行查询语句
func (s *SQLServerDriverImpl) ExecuteQuery(ctx context.Context, query string, args ...interface{}) (*models.QueryResult, error) {
	if !s.IsConnected() {
		return nil, ErrNotConnected
	}

	start := time.Now()
	rows, err := s.DB.QueryContext(ctx, query, args...)
	if err != nil {
		return &models.QueryResult{
			Error:       err.Error(),
			ExecuteTime: time.Since(start),
		}, nil
	}
	defer rows.Close()

	// 获取列名
	columns, err := rows.Columns()
	if err != nil {
		return nil, errors.Wrap(err, "failed to get columns")
	}

	// 读取数据
	var result []map[string]interface{}
	for rows.Next() {
		values := make([]interface{}, len(columns))
		valuePtrs := make([]interface{}, len(columns))
		for i := range values {
			valuePtrs[i] = &values[i]
		}

		if err := rows.Scan(valuePtrs...); err != nil {
			return nil, errors.Wrap(err, "failed to scan row")
		}

		row := make(map[string]interface{})
		for i, col := range columns {
			val := values[i]

			// 处理特殊类型
			if val != nil {
				// 处理byte数组
				if b, ok := val.([]byte); ok {
					// 默认转换为字符串
					row[col] = string(b)
				} else {
					// 处理int64类型，确保不超出JavaScript安全整数范围
					if i64, ok := val.(int64); ok && (i64 > 9007199254740991 || i64 < -9007199254740991) {
						row[col] = strconv.FormatInt(i64, 10)
					} else {
						row[col] = val
					}
				}
			} else {
				row[col] = nil
			}
		}
		result = append(result, row)
	}

	return &models.QueryResult{
		Columns:     columns,
		Rows:        result,
		RowCount:    len(result),
		ExecuteTime: time.Since(start),
	}, nil
}

// ExecuteNonQuery 执行非查询语句
func (s *SQLServerDriverImpl) ExecuteNonQuery(ctx context.Context, query string, args ...interface{}) (int64, error) {
	if !s.IsConnected() {
		return 0, ErrNotConnected
	}

	result, err := s.DB.ExecContext(ctx, query, args...)
	if err != nil {
		return 0, errors.Wrap(err, "failed to execute non-query")
	}

	affected, err := result.RowsAffected()
	if err != nil {
		return 0, errors.Wrap(err, "failed to get affected rows")
	}

	return affected, nil
}

// BeginTransaction 开始事务
func (s *SQLServerDriverImpl) BeginTransaction(ctx context.Context) (DatabaseTransaction, error) {
	if !s.IsConnected() {
		return nil, ErrNotConnected
	}

	tx, err := s.DB.BeginTx(ctx, nil)
	if err != nil {
		return nil, errors.Wrap(err, "failed to begin transaction")
	}

	return &SQLServerTransaction{tx: tx}, nil
}

// SQLServerTransaction SQL Server事务实现
type SQLServerTransaction struct {
	tx *sql.Tx
}

func (st *SQLServerTransaction) Commit() error {
	return st.tx.Commit()
}

func (st *SQLServerTransaction) Rollback() error {
	return st.tx.Rollback()
}

func (st *SQLServerTransaction) ExecuteQuery(ctx context.Context, query string, args ...interface{}) (*models.QueryResult, error) {
	start := time.Now()
	rows, err := st.tx.QueryContext(ctx, query, args...)
	if err != nil {
		return &models.QueryResult{
			Error:       err.Error(),
			ExecuteTime: time.Since(start),
		}, nil
	}
	defer rows.Close()

	columns, err := rows.Columns()
	if err != nil {
		return nil, errors.Wrap(err, "failed to get columns")
	}

	var result []map[string]interface{}
	for rows.Next() {
		values := make([]interface{}, len(columns))
		valuePtrs := make([]interface{}, len(columns))
		for i := range values {
			valuePtrs[i] = &values[i]
		}

		if err := rows.Scan(valuePtrs...); err != nil {
			return nil, errors.Wrap(err, "failed to scan row")
		}

		row := make(map[string]interface{})
		for i, col := range columns {
			val := values[i]

			// 处理特殊类型
			if val != nil {
				// 处理byte数组
				if b, ok := val.([]byte); ok {
					// 默认转换为字符串
					row[col] = string(b)
				} else {
					// 处理int64类型，确保不超出JavaScript安全整数范围
					if i64, ok := val.(int64); ok && (i64 > 9007199254740991 || i64 < -9007199254740991) {
						row[col] = strconv.FormatInt(i64, 10)
					} else {
						row[col] = val
					}
				}
			} else {
				row[col] = nil
			}
		}
		result = append(result, row)
	}

	return &models.QueryResult{
		Columns:     columns,
		Rows:        result,
		RowCount:    len(result),
		ExecuteTime: time.Since(start),
	}, nil
}

func (st *SQLServerTransaction) ExecuteNonQuery(ctx context.Context, query string, args ...interface{}) (int64, error) {
	result, err := st.tx.ExecContext(ctx, query, args...)
	if err != nil {
		return 0, errors.Wrap(err, "failed to execute non-query in transaction")
	}

	affected, err := result.RowsAffected()
	if err != nil {
		return 0, errors.Wrap(err, "failed to get affected rows in transaction")
	}

	return affected, nil
}

// Exec 执行SQL命令，返回sql.Result
func (st *SQLServerTransaction) Exec(query string, args ...interface{}) (sql.Result, error) {
	if st.tx == nil {
		return nil, errors.New("transaction is nil or already committed")
	}

	return st.tx.Exec(query, args...)
}
